IF OBJECT_ID('dbo.csp_rptMetric_SummaryThisPeriod') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptMetric_SummaryThisPeriod
END
GO

CREATE PROCEDURE dbo.csp_rptMetric_SummaryThisPeriod
(
	@PeriodType varchar(50)
,	@PeriodStartDate smalldatetime
)
AS

SELECT
	Institution
,	PeriodType
,	PeriodStartDate
,	PendingSubmissions = SUM(CASE WHEN Subject='Pending Submissions' THEN 1 ELSE 0 END)
,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
,	PreAwardJIT = SUM(CASE WHEN Subject='Pre-Award JIT' THEN 1 ELSE 0 END)
,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
,	AdvanceAccount = SUM(CASE WHEN Subject='Advance Account' THEN 1 ELSE 0 END)
,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
,	AwardedButNoAttachment = SUM(CASE WHEN Subject='Awarded But No Attachment' THEN 1 ELSE 0 END)
,	PCROAwardedButNoAttachment = SUM(CASE WHEN Subject='PCRO Awarded But No Attachment' THEN 1 ELSE 0 END)
,	AwardedButNotFunded = SUM(CASE WHEN Subject='Awarded But Not Funded' THEN 1 ELSE 0 END)
,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
,	RenewalsToBeProcessed = SUM(CASE WHEN Subject='Renewals To Be Processed' THEN 1 ELSE 0 END)
,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
,	ProjectsEnding30 = SUM(CASE WHEN Subject='Projects Ending 30' THEN 1 ELSE 0 END)
,	ProjectsEnding60 = SUM(CASE WHEN Subject='Projects Ending 60' THEN 1 ELSE 0 END)
,	ProjectsEnding90 = SUM(CASE WHEN Subject='Projects Ending 90' THEN 1 ELSE 0 END)
FROM
(
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Accounts Inactivated'
	FROM
		[dbo].[rptMetric_AccountsInactivated]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Advance Account'
	FROM
		[dbo].[rptMetric_AdvanceAccount]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Advance Account Week'
	FROM
		[dbo].[rptMetric_AdvanceAccountWeek]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awards Activated'
	FROM
		[dbo].[rptMetric_AwardActivations]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awarded But No Attachment'
	FROM
		[dbo].[rptMetric_AwardedButNoAttachment]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awarded But Not Funded'
	FROM
		[dbo].[rptMetric_AwardedButNotFunded]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Awards To Be Activated'
	FROM
		[dbo].[rptMetric_AwardsToBeActivated]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'PCRO Awarded But No Attachment'
	FROM
		[dbo].[rptMetric_PCROAwardedButNoAttachment]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'PCRO Awards To Be Activated'
	FROM
		[dbo].[rptMetric_PCROAwardsToBeActivated]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Pending Submissions'
	FROM
		[dbo].[rptMetric_PendingSubmissions]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Pre-Award JIT'
	FROM
		[dbo].[rptMetric_PreAwardJIT]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Pre-Award JIT Week'
	FROM
		[dbo].[rptMetric_PreAwardJITWeek]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Projects Ending 30'
	FROM
		[dbo].[rptMetric_ProjectsEnding30]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Projects Ending 60'
	FROM
		[dbo].[rptMetric_ProjectsEnding60]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Projects Ending 90'
	FROM
		[dbo].[rptMetric_ProjectsEnding90]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Renewals Processed'
	FROM
		[dbo].[rptMetric_RenewalsProcessed]
	UNION ALL
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	Subject = 'Renewals To Be Processed'
	FROM
		[dbo].[rptMetric_RenewalsToBeProcessed]
) AS x
WHERE
	1=1
AND	PeriodType = @PeriodType
AND	PeriodStartDate = @PeriodStartDate
GROUP BY
	Institution
,	PeriodType
,	PeriodStartDate
GO
